﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Access.PowerTools.AbstractionLayer;
using SMSConsulting.SMSLIB.Utilities;

using acc = Access;
using dao = DAO;

using System.Globalization;
using System.Data.OleDb;

namespace Access.PowerTools.Add_In.Engine
{
    public partial class AddInCommunicationBridge : IHostComunicationBridge
    {

        public enum AcImportXMLOption
        {
            acStructureOnly = 0,
            acStructureAndData = 1,
            acAppendData = 2,
        }

        public enum AcExportXMLObjectType
        {
            acExportTable = 0,
            acExportQuery = 1,
            acExportForm = 2,
            acExportReport = 3,
            acExportServerView = 7,
            acExportStoredProcedure = 9,
            acExportFunction = 10,
        }

        public enum AcExportXMLEncoding
        {
            acUTF8 = 0,
            acUTF16 = 1,
        }

        public enum AcExportXMLOtherFlags
        {
            acEmbedSchema = 1,
            acExcludePrimaryKeyAndIndexes = 2,
            acRunFromServer = 4,
            acLiveReportSource = 8,
            acPersistReportML = 16,
            acExportAllTableAndFieldProperties = 32,
        }

        private CultureInfo _savedCultureInfo;

        private acc.Application _application;

        private void setThreadCultureInfo()
        {
            System.Threading.Thread thisThread =
                System.Threading.Thread.CurrentThread;
            _savedCultureInfo =
                thisThread.CurrentCulture;
            thisThread.CurrentCulture = new System.Globalization.CultureInfo(
                    "en-US");
        }
        private void restoreThreadCultureInfo()
        {
            System.Threading.Thread.CurrentThread.CurrentCulture = _savedCultureInfo;
        }

        /// <summary>
        /// Sets host application - used by add-ins or
        /// automation with existing MS Access application
        /// </summary>
        /// <param name="app"></param>
        public void SetHostApplication(object app)
        {
            _application = app as acc.Application;
        }

        public bool StartHostApplication()
        {
            try
            {
                setThreadCultureInfo();

                _application = new acc.Application();
                _application.Visible = true;

                return true;
            }
            catch (Exception ex)
            {
                SimpleLogWriter.WriteLogLine("StartHostApplication: " + ex.Message);
                return false;
            }
            finally { restoreThreadCultureInfo(); }
        }

        public bool QuitHostApplication()
        {
            try
            {
                setThreadCultureInfo();

                _application.Quit(acc.AcQuitOption.acQuitSaveNone);
                return true;
            }
            catch (Exception ex)
            {
                SimpleLogWriter.WriteLogLine("QuitHostApplication: " + ex.Message);
                return false;
            }
            finally { restoreThreadCultureInfo(); }
        }

        private string _currentDatabaseFullPath;
        public bool OpenCurrentDatabase(string databaseFullPath)
        {
            try
            {
                setThreadCultureInfo();

                _currentDatabaseFullPath = databaseFullPath;

                //_application.OpenCurrentDatabase(databaseFullPath, false, null);
                //_application.OpenCurrentDatabaseOld(databaseFullPath, false);

                object[] args =
                {
                    databaseFullPath, false,
                    System.Reflection.Missing.Value  
                };
                Type access = _application.GetType();
                access.InvokeMember(
                    "OpenCurrentDatabase",
                     System.Reflection.BindingFlags.InvokeMethod |
                     System.Reflection.BindingFlags.IgnoreCase |
                     System.Reflection.BindingFlags.IgnoreReturn,
                     null,
                     _application,
                     args);
                return true;
            }
            catch (Exception ex)
            {
                SimpleLogWriter.WriteLogLine("OpenCurrentDatabase: " + ex.Message);
                return false;
            }
            finally { restoreThreadCultureInfo(); }
        }

        public bool CloseCurrentDatabase()
        {
            try
            {
                setThreadCultureInfo();
                _application.CloseCurrentDatabase();

                _objects = null;
                _database = null;

                GC.Collect();

                return true;
            }
            catch (Exception ex)
            {
                SimpleLogWriter.WriteLogLine("CloseCurrentDatabase: " + ex.Message);
                return false;
            }
            finally { restoreThreadCultureInfo(); }
        }

        public bool CompactCurrentDatabase()
        {
            dao.DBEngineClass dbEngine = null;

            try
            {
                dbEngine = new dao.DBEngineClass();

                setThreadCultureInfo();

                CloseCurrentDatabase();

                System.Threading.Thread.Sleep(1000);

                string currentDbFolder = System.IO.Path.GetDirectoryName(_currentDatabaseFullPath);
                string currentDbName = System.IO.Path.GetFileName(_currentDatabaseFullPath);
                string tempDbFullPath = System.IO.Path.Combine(currentDbFolder, "~tempDb.mdb");

                Console.WriteLine("Compacting...");
                Object srcLocale = dao.LanguageConstants.dbLangGeneral;
                Object dstLocale = dao.LanguageConstants.dbLangGeneral;
                Object compactOptions = System.Reflection.Missing.Value;

                dbEngine.CompactDatabase(_currentDatabaseFullPath, tempDbFullPath,
                      dstLocale, compactOptions, srcLocale);

                string copyDbFullPath = System.IO.Path.Combine(currentDbFolder, "~" + currentDbName);

                Console.WriteLine("Moving...");
                System.IO.File.Move(_currentDatabaseFullPath, copyDbFullPath);
                System.IO.File.Move(tempDbFullPath, _currentDatabaseFullPath);

                Console.WriteLine("Deleting...");
                System.IO.File.Delete(copyDbFullPath);

                _objects = null;
                _database = null;

                return true;
            }
            catch (Exception ex)
            {
                //LOG: 14/02/2010 19:40:18 - CompactCurrentDatabase: You attempted to open a database that is already opened exclusively by user 'Admin' on machine 'HAMBURG'.  Try again when the database is available.
                //LOG: 14/02/2010 19:40:18 - CompactCurrentDatabase:    at Microsoft.Office.Interop.Access.ApplicationClass.CompactRepair(String SourceFile, String DestinationFile, Boolean LogFile)
                //at Access.PowerTools.Automation.Engine.AutomationCommunicationBridge.CompactCurrentDatabase() in E:\MyProjects\Add-In-Express\Access.PowerTools\Code\Access.PowerTools.Automation.Engine\AutomationCommunicationBridge.cs:line 127

                SimpleLogWriter.WriteLogLine("CompactCurrentDatabase: " + ex.Message);
                SimpleLogWriter.WriteLogLine("CompactCurrentDatabase: " + ex.StackTrace);
                return false;
            }
            finally { restoreThreadCultureInfo(); }
        }






        #region IHostComunicationBridge Members
        string IHostComunicationBridge.ApplicationName
        {
            get { return _application.Name; }
        }

        string IHostComunicationBridge.ApplicationVersion
        {
            get
            {
                //return _application.Version;
                Object missing = System.Reflection.Missing.Value;
                return (string)_application.SysCmd(acc.AcSysCmdAction.acSysCmdAccessVer, missing, missing);
            }
        }

        private IHostComunicationBridge iHostBridge { get { return this; } }

        private AccessDatabase _database;

        AccessDatabase IHostComunicationBridge.CurrentDatabase
        {
            get
            {
                if (_database == null) initDatabase();
                return _database;
            }
        }

        string IHostComunicationBridge.CurrentDatabaseFullPath
        {
            get
            {
                dao.Database dbs = _application.CurrentDb();
                string fullPath = dbs.Name;
                dbs = null;
                return fullPath;
            }
        }

        private List<AccessDatabaseObjectInfo> _objects;
        private void initDatabase()
        {
            dao.Database dbs = null;

            try
            {
                dbs = _application.CurrentDb();

                if (dbs == null)
                {
                    throw new ApplicationException(
                        string.Format("AutomationCommunicationBridge.initDatabase: CurrentDatabase is not available, dbs = null")
                        );
                }

                _objects = getAllObjectsInfo(dbs);
            }
            catch { }
            finally
            {
                dbs = null;
                _database = new AccessDatabase(this);
            }

        }

        List<AccessDatabaseObjectInfo> IHostComunicationBridge.GetObjectsList()
        {
            if (_database == null) initDatabase();
            return _objects;
        }


        bool IHostComunicationBridge.ObjectExists(AccessDataBaseObjectTypeEnumeration objectType, string objectName)
        {
            AccessDatabase database = this.iHostBridge.CurrentDatabase;
            if (database == null) return false;

            AccessDatabaseObjectInfo objectInfo = database.GetObjectInfo(objectType, objectName);

            return (objectInfo != null);
        }

        void IHostComunicationBridge.SaveAsText(
            AccessDataBaseObjectTypeEnumeration objectType,
            string objectName,
            string backUpFileFullPath)
        {
            AccessDatabase database = this.iHostBridge.CurrentDatabase;
            AccessDatabaseObjectInfo objectInfo =
                database.GetObjectInfo(objectType, objectName);
            RunTimeAssert.NotNull(objectInfo,
                string.Format("Can't find object to backup {0} => {1}", objectType, objectName));

            if (objectType != AccessDataBaseObjectTypeEnumeration.Table)
            {
                _application.SaveAsText(
                 (acc.AcObjectType)(int)objectType,
                 objectName,
                 backUpFileFullPath);
            }
            else // (objectType == AccessDataBaseObjectTypeEnumeration.Table)
            {
                string sourceFolder = System.IO.Path.GetDirectoryName(objectInfo.SimulationSeedObjectInfoFileFullPath) +
                                      System.IO.Path.DirectorySeparatorChar;
                string backUpFolder = System.IO.Path.GetDirectoryName(backUpFileFullPath) +
                                      System.IO.Path.DirectorySeparatorChar;

                string fileName = AccessDatabaseObjectInfo.GetBackUpFileNameForObjectSchema(objectType, objectName);
                //_application.ExportXMLOld(
                //      acc.AcExportXMLObjectType.acExportTable,
                //      objectName, 
                //      backUpFileFullPath,
                //      backUpFolder + fileName,
                //      null,null, 
                //      acc.AcExportXMLEncoding.acUTF8,
                //      0);

                object[] args = 
                {
                      AcExportXMLObjectType.acExportTable,
                      objectName, 
                      backUpFileFullPath,
                      backUpFolder + fileName,
                      null,
                      null, 
                      AcExportXMLEncoding.acUTF8,
                      //AcExportXMLOtherFlags.acEmbedSchema |
                      AcExportXMLOtherFlags.acExportAllTableAndFieldProperties,
                      System.Reflection.Missing.Value, // where expression
                      System.Reflection.Missing.Value // Additional Data
                }
                ;
                Type access = _application.GetType();
                access.InvokeMember(
                     "ExportXML",
                     System.Reflection.BindingFlags.InvokeMethod |
                     System.Reflection.BindingFlags.IgnoreCase |
                     System.Reflection.BindingFlags.IgnoreReturn,
                     null,
                     _application,
                     args);

                fileName = AccessDatabaseObjectInfo.GetBackUpFileNameForDelimitedTextExport(objectType, objectName);

                ////object[] 
                //args = new object[]
                //{
                //    acc.AcTextTransferType.acExportDelim,
                //    System.Reflection.Missing.Value,
                //    objectName,
                //    backUpFolder + fileName,
                //    (object)true,
                //    System.Reflection.Missing.Value,
                //    System.Reflection.Missing.Value
                //};
                //Type doCmd = _application.DoCmd.GetType();
                //doCmd.InvokeMember(
                //     "TransferText",
                //     System.Reflection.BindingFlags.InvokeMethod |
                //     System.Reflection.BindingFlags.IgnoreCase |
                //     System.Reflection.BindingFlags.IgnoreReturn,
                //     null,
                //     _application,
                //     args);

                _application.DoCmd.TransferText(
                    acc.AcTextTransferType.acExportDelim,
                    System.Reflection.Missing.Value,
                    objectName,
                    backUpFolder + fileName,
                    (object)true,
                    System.Reflection.Missing.Value,
                    System.Reflection.Missing.Value
                    );
            }
        }

        void IHostComunicationBridge.LoadFromText(
            AccessDataBaseObjectTypeEnumeration objectType,
            string objectName,
            string backUpFileFullPath)
        {
            AccessDatabaseObjectInfo objectInfo =
                this.iHostBridge.CurrentDatabase.GetObjectInfo(objectType, objectName);
            string sql = "";
            dao.Database dbs = _application.CurrentDb();

            if (objectInfo == null)
            {
                objectInfo = new AccessDatabaseObjectInfo(objectType, objectName);
            }

            if (objectType != AccessDataBaseObjectTypeEnumeration.Table)
            {
                _application.LoadFromText(
                    (acc.AcObjectType)(int)objectType,
                    objectName, backUpFileFullPath);
            }
            else //(objectType == AccessDataBaseObjectTypeEnumeration.Table)
            {
                string backUpFolder = System.IO.Path.GetDirectoryName(backUpFileFullPath);

                string fileName = AccessDatabaseObjectInfo.GetBackUpFileNameForObjectSchema(objectType, objectName);


                if (!tableExists(dbs, objectName))
                {
                    //_application.ImportXML(
                    //  System.IO.Path.Combine(backUpFolder, fileName),
                    //  acc.AcImportXMLOption.acStructureAndData);

                    object[] args = 
                    {
                          System.IO.Path.Combine(backUpFolder, fileName),
                          AcImportXMLOption.acStructureAndData
                    }
                    ;
                    Type access = _application.GetType();
                    access.InvokeMember(
                         "ImportXML",
                         System.Reflection.BindingFlags.InvokeMethod |
                         System.Reflection.BindingFlags.IgnoreCase |
                         System.Reflection.BindingFlags.IgnoreReturn,
                         null,
                         _application,
                         args);
                }
                else
                {
                    sql = string.Format("delete * from [{0}]", objectName);
                    object options = System.Reflection.Missing.Value;
                    dbs.Execute(sql, options);

                    //+
                    //_application.ImportXML(
                    //    System.IO.Path.Combine(backUpFolder, fileName),
                    //    acc.AcImportXMLOption.acAppendData);
                    object[] args = 
                    {
                          System.IO.Path.Combine(backUpFolder, fileName),
                          AcImportXMLOption.acAppendData
                    }
                    ;
                    Type access = _application.GetType();
                    access.InvokeMember(
                         "ImportXML",
                         System.Reflection.BindingFlags.InvokeMethod |
                         System.Reflection.BindingFlags.IgnoreCase |
                         System.Reflection.BindingFlags.IgnoreReturn,
                         null,
                         _application,
                         args);
                    //+
                }

                // if table didn't get data
                bool hasData = objectHasData(dbs, objectName);

                if (!hasData)
                {
                    fileName = AccessDatabaseObjectInfo.GetBackUpFileNameForDelimitedTextExport(objectType, objectName);

                    acc.DoCmd doCmd = null;
                    try
                    {
                        doCmd = _application.DoCmd;

                        doCmd.TransferText(
                            acc.AcTextTransferType.acImportDelim,
                            System.Reflection.Missing.Value,
                            objectName,
                            System.IO.Path.Combine(backUpFolder, fileName),
                            (object)true,
                            System.Reflection.Missing.Value,
                            System.Reflection.Missing.Value
                            );
                    }
                    catch (Exception ex)
                    {
                        string message = "";
                        if (ex.InnerException != null)
                            message = ex.InnerException.Message;
                        if (!string.IsNullOrEmpty(message)) message += " *** " + ex.Message;
                        else message = ex.Message;

                        string.Format("acImportDelim - {0}: {1}", objectName, ex.Message);

                        SimpleLogWriter.WriteLogLine(message);
                        Console.WriteLine(message);
                    }
                    finally
                    {
                        doCmd = null;
                        GC.Collect();
                    }
                }

                Console.WriteLine("{0}: {1} rows imported.", objectName, objectRowsCount(dbs, objectName));
                //
            }
        }

        #endregion

        private bool objectHasData(dao.Database dbs, string objectName)
        {
            return (objectRowsCount(dbs, objectName) > 0);
        }

        private int objectRowsCount(dao.Database dbs, string objectName)
        {
            int count = 0;
            string sql = string.Format("select count(*) from [{0}]", objectName);
            dao.Recordset rst = dbs.OpenRecordset(
                 sql,
                 dao.RecordsetTypeEnum.dbOpenSnapshot,
                 dao.RecordsetOptionEnum.dbForwardOnly,
                 System.Reflection.Missing.Value);
            if (rst != null)
            {
                count = (int)rst.Fields[0].Value;

                //System.Console.WriteLine("{0} - {1} - {2}", objectName, count, hasData);

                rst.Close();
            }
            rst = null;

            return count;
        }
        public List<AccessDatabaseObjectInfo> GetObjectsInfo(string accessDbFullPath)
        {
            //OBJECT OF MISSING "NULL VALUE"
            Object oMissing = System.Reflection.Missing.Value;

            //OBJECTS OF FALSE AND TRUE
            Object oTrue = true;
            Object oFalse = false;
            Object oNull = null;

            _currentDatabaseFullPath = accessDbFullPath;

            dao.DBEngineClass dbEngine = null;
            dao.Database dbs = null;

            try
            {
                dbEngine = new dao.DBEngineClass();

                dbs = dbEngine.OpenDatabase(accessDbFullPath, oMissing, oFalse, oNull);

                return getAllObjectsInfo(dbs);

            }
            catch (Exception ex)
            {
                string message = "";
                if (ex.InnerException != null)
                    message = ex.InnerException.Message;
                if (!string.IsNullOrEmpty(message))
                {
                    message += System.Environment.NewLine +
                               "====" +
                               System.Environment.NewLine;
                }
                message += ex.Message;

                SimpleLogWriter.WriteLogLine("AutomationCommunicationBridge.GetObjectsInfo: " +
                                message);
                throw;
            }
            finally
            {
                if (dbs != null) dbs.Close();
                dbs = null;
                dbEngine = null;

                GC.Collect();
            }


        }

        private void getContainerObjects(
             AccessDataBaseObjectTypeEnumeration objectType,
             dao.Container container,
            ref List<AccessDatabaseObjectInfo> list)
        {
            if (container != null)
            {
                dao.Documents docs = container.Documents;
                for (int i = 0; i < docs.Count; i++)
                {
                    dao.Document doc = docs[i];

                    AccessDatabaseObjectInfo objectInfo =
                        new AccessDatabaseObjectInfo(
                            objectType,
                            doc.Name);
                    doc = null;
                    list.Add(objectInfo);
                }
            }
        }

        private List<AccessDatabaseObjectInfo> getAllObjectsInfo(dao.Database dbs)
        {
            List<AccessDatabaseObjectInfo> list = new List<AccessDatabaseObjectInfo>();

            if (_objects == null) _objects = new List<AccessDatabaseObjectInfo>();

            dao.Containers containers = dbs.Containers;

            for (int i = 0; i < containers.Count; i++)
            {
                dao.Container con = containers[i];

                if (string.Compare(con.Name, "Forms", true) == 0)
                    getContainerObjects(AccessDataBaseObjectTypeEnumeration.Form, con, ref list);
                if (string.Compare(con.Name, "Reports", true) == 0)
                    getContainerObjects(AccessDataBaseObjectTypeEnumeration.Report, con, ref list);
                if (string.Compare(con.Name, "Scripts", true) == 0)
                    getContainerObjects(AccessDataBaseObjectTypeEnumeration.Macro, con, ref list);
                if (string.Compare(con.Name, "Modules", true) == 0)
                    getContainerObjects(AccessDataBaseObjectTypeEnumeration.Module, con, ref list);

                if (string.Compare(con.Name, "Tables", true) == 0)
                {
                    List<AccessDatabaseObjectInfo> list1 = new List<AccessDatabaseObjectInfo>();
                    getContainerObjects(AccessDataBaseObjectTypeEnumeration.Table, con, ref list1);

                    foreach (AccessDatabaseObjectInfo objectInfo in list1)
                    {
                        if (objectInfo.Name.StartsWith("~")) continue;
                        if (objectInfo.Name.StartsWith("MSys")) continue;

                        if (tableExists(dbs, objectInfo.Name))
                            list.Add(objectInfo);
                        else
                        {
                            list.Add(
                                new AccessDatabaseObjectInfo(AccessDataBaseObjectTypeEnumeration.Query,
                                    objectInfo.Name));
                        }
                    }
                }
                con = null;
            }

            return list;
        }

        private bool tableExists(dao.Database dbs, string tableName)
        {
            dao.TableDefs tdfs = null;
            dao.TableDef tdf = null;
            try
            {
                tdfs = dbs.TableDefs;
                tdf = tdfs[tableName];
                return (tdf != null);
            }
            catch { return false; }
            finally
            {
                tdf = null;
                tdfs = null;
            }
        }


    }
}
